using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using CC.Utilities;
using CC.Yacht.Exceptions;

namespace CC.Yacht.Server
{
    public static class Database
    {
        #region Private Constants
        private const string ConnectionString = "CC.Yacht.ConnectionString";
        #endregion

        #region Private Methods
        private static SqlConnection GetSqlConnection()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString);
        }

        private static ExtendedUser GetUserFromDataReader(DbDataReader dataReader)
        {
            ExtendedUser returnValue = null;

            if (dataReader != null && dataReader.HasRows && dataReader.Read())
            {
                //NOTE: All stored procedures must return the following column order for users: (0) UserId, (1) Email, (2) Password, (3) Username, (4) IsDeleted, (5) Created

                long tempUserId = (!dataReader.IsDBNull(0) ? dataReader.GetInt64(0) : -1);
                bool tempIsDeleted = (!dataReader.IsDBNull(4) ? dataReader.GetBoolean(4) : false);
                DateTime tempCreated = (!dataReader.IsDBNull(5) ? dataReader.GetDateTime(5) : DateTime.MinValue);

                ExtendedUser tempUser = new ExtendedUser(tempUserId, tempIsDeleted, tempCreated)
                {
                    Email = (!dataReader.IsDBNull(1) ? dataReader.GetString(1) : string.Empty),
                    IsPasswordEncrypted = true,
                    Password = (!dataReader.IsDBNull(2) ? dataReader.GetString(2) : string.Empty),
                    Username = (!dataReader.IsDBNull(3) ? dataReader.GetString(3) : string.Empty)
                };

                if (tempUser.IsValid)
                {
                    returnValue = tempUser;
                }
            }

            return returnValue;
        }
        #endregion

        #region Public Methods
        public static bool AddUser(ExtendedUser user)
        {
            if (!user.IsValid)
            {
                throw new InvalidUserException();
            }

            if (GetUser(user.Email) != null)
            {
                throw new DuplicateUserException();
            }

            using (SqlConnection sqlConnection = GetSqlConnection())
            {
                using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "AddUser";
                    sqlCommand.Parameters.AddWithValue("@Email", user.Email);
                    sqlCommand.Parameters.AddWithValue("@Password", (user.IsPasswordEncrypted) ? user.Password : Encryption.Encrypt(user.Password, user.Password));
                    sqlCommand.Parameters.AddWithValue("@Username", user.Username);
                    sqlConnection.Open();

                    return (sqlCommand.ExecuteNonQuery() > 0);
                }
            }
        }

        public static ExtendedUser GetUser(string email)
        {
            ExtendedUser returnValue;

            using (SqlConnection sqlConnection = GetSqlConnection())
            {
                using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "GetUserByEmail";
                    sqlCommand.Parameters.AddWithValue("@Email", email);
                    sqlConnection.Open();

                    using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        returnValue = GetUserFromDataReader(sqlDataReader);
                    }
                }
            }

            return returnValue;
        }

        public static ExtendedUser GetUser(long userId)
        {
            ExtendedUser returnValue;

            using (SqlConnection sqlConnection = GetSqlConnection())
            {
                using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "GetUserByUserId";
                    sqlCommand.Parameters.AddWithValue("@UserId", userId);
                    sqlConnection.Open();

                    using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        returnValue = GetUserFromDataReader(sqlDataReader);
                    }
                }
            }

            return returnValue;
        }
        #endregion
    }
}